"""
数据源相关模型
"""
from sqlalchemy import Column, Integer, String, Boolean, DateTime, Text, ForeignKey, JSON
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from enum import Enum
from typing import Optional, Dict, Any
from datetime import datetime

from .base import Base, TimestampMixin, ActiveMixin


class DataSourceType(str, Enum):
    """数据源类型枚举"""
    DATABASE = "database"
    EXCEL = "excel"
    CSV = "csv"
    API = "api"


class DatabaseType(str, Enum):
    """数据库类型枚举"""
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"
    SQLITE = "sqlite"
    ORACLE = "oracle"
    SQLSERVER = "sqlserver"


class DataSourceStatus(str, Enum):
    """数据源状态枚举"""
    ACTIVE = "active"
    INACTIVE = "inactive"
    ERROR = "error"
    TESTING = "testing"


class DataSource(Base, TimestampMixin, ActiveMixin):
    """数据源模型"""
    
    __tablename__ = "data_source"
    
    # 基本信息
    name = Column(String(100), nullable=False, comment="数据源名称")
    description = Column(Text, comment="数据源描述")
    source_type = Column(String(20), nullable=False, comment="数据源类型")
    status = Column(String(20), default=DataSourceStatus.INACTIVE, comment="状态")
    
    # 统计信息
    record_count = Column(Integer, default=0, comment="记录数量")
    last_sync_time = Column(DateTime, comment="最后同步时间")
    last_sync_result = Column(String(255), comment="最后同步结果")
    
    # 预览配置
    preview_enabled = Column(Boolean, default=True, comment="是否启用预览")
    preview_limit = Column(Integer, default=100, comment="预览行数限制")
    last_preview_time = Column(DateTime, comment="最后预览时间")
    
    # 连接配置
    connection_timeout = Column(Integer, default=30, comment="连接超时时间(秒)")
    max_retry_attempts = Column(Integer, default=3, comment="最大重试次数")
    
    # 标签和分类
    tags = Column(JSON, comment="数据源标签")
    category = Column(String(50), comment="数据源分类")
    priority = Column(Integer, default=0, comment="优先级")
    
    # 创建者
    creator_id = Column(Integer, ForeignKey('user.id'), comment="创建者ID")
    
    # 关系
    # creator = relationship("User", back_populates="data_sources")
    database_config = relationship("DatabaseConfig", back_populates="data_source", uselist=False, cascade="all, delete-orphan")
    excel_config = relationship("ExcelConfig", back_populates="data_source", uselist=False, cascade="all, delete-orphan")
    analysis_tasks = relationship("AnalysisTask", back_populates="data_source")
    sync_logs = relationship("DataSourceSyncLog", back_populates="data_source")
    preview_logs = relationship("DataSourcePreviewLog", back_populates="data_source")
    
    def get_config(self) -> Optional[Dict[str, Any]]:
        """获取配置信息"""
        if self.source_type == DataSourceType.DATABASE:
            return self.database_config.to_dict() if self.database_config else None
        elif self.source_type == DataSourceType.EXCEL:
            return self.excel_config.to_dict() if self.excel_config else None
        return None
    
    def update_status(self, status: DataSourceStatus, result: str = None):
        """更新状态"""
        self.status = status
        if result:
            self.last_sync_result = result
        if status == DataSourceStatus.ACTIVE:
            self.last_sync_time = func.now()
    
    @property
    def config(self) -> Optional[Dict[str, Any]]:
        """获取配置信息"""
        return self.get_config()
    
    @property
    def last_connected(self) -> Optional[datetime]:
        """获取最后连接时间"""
        return self.last_sync_time
    
    @property
    def connection_info(self) -> Optional[Dict[str, Any]]:
        """获取连接信息"""
        return None if not self.last_sync_result else {"status": self.status, "message": self.last_sync_result}
    
    def to_dict(self) -> dict:
        """转换为字典"""
        result = super().to_dict()
        result['config'] = self.get_config()
        return result


class DatabaseConfig(Base, TimestampMixin):
    """数据库配置模型"""
    
    __tablename__ = "database_config"
    
    # 关联
    data_source_id = Column(Integer, ForeignKey('data_source.id', ondelete="CASCADE"), nullable=False, unique=True)
    
    # 连接信息
    db_type = Column(String(20), nullable=False, comment="数据库类型")
    host = Column(String(255), comment="主机地址")
    port = Column(Integer, comment="端口")
    database = Column(String(100), comment="数据库名称")
    username = Column(String(100), comment="用户名")
    password = Column(String(255), comment="密码")
    
    # 高级配置
    charset = Column(String(20), default="utf8mb4", comment="字符集")
    timeout = Column(Integer, default=30, comment="连接超时时间")
    ssl_mode = Column(String(20), comment="SSL模式")
    
    # 表信息
    table_name = Column(String(100), comment="表名")
    schema_name = Column(String(100), comment="模式名")
    
    # 字段映射
    field_mapping = Column(JSON, comment="字段映射配置")
    
    # 关系
    data_source = relationship("DataSource", back_populates="database_config")
    
    def get_connection_string(self) -> str:
        """获取数据库连接字符串"""
        from urllib.parse import quote_plus
        
        if self.db_type == DatabaseType.MYSQL:
            encoded_password = quote_plus(self.password) if self.password else ""
            return f"mysql+pymysql://{self.username}:{encoded_password}@{self.host}:{self.port}/{self.database}"
        elif self.db_type == DatabaseType.POSTGRESQL:
            encoded_password = quote_plus(self.password) if self.password else ""
            return f"postgresql+psycopg2://{self.username}:{encoded_password}@{self.host}:{self.port}/{self.database}"
        elif self.db_type == DatabaseType.SQLITE:
            return f"sqlite:///{self.database}"
        elif self.db_type == DatabaseType.ORACLE:
            encoded_password = quote_plus(self.password) if self.password else ""
            return f"oracle+cx_oracle://{self.username}:{encoded_password}@{self.host}:{self.port}/{self.database}"
        elif self.db_type == DatabaseType.SQLSERVER:
            encoded_password = quote_plus(self.password) if self.password else ""
            return f"mssql+pyodbc://{self.username}:{encoded_password}@{self.host}:{self.port}/{self.database}"
        else:
            raise ValueError(f"Unsupported database type: {self.db_type}")
    
    @classmethod
    def from_config(cls, data_source_id: int, config: Dict[str, Any]) -> 'DatabaseConfig':
        """从配置字典创建DatabaseConfig实例"""
        # 处理字段名映射
        normalized_config = config.copy()
        if "database_type" in normalized_config:
            normalized_config["db_type"] = normalized_config.pop("database_type")
        if "type" in normalized_config:
            normalized_config["db_type"] = normalized_config.pop("type")
        
        # 处理 schema 字段映射
        if "schema" in normalized_config:
            normalized_config["schema_name"] = normalized_config.pop("schema")
        
        # 移除不存在的字段，避免创建实例时出错
        valid_fields = [column.name for column in cls.__table__.columns]
        filtered_config = {k: v for k, v in normalized_config.items() if k in valid_fields}
        
        return cls(data_source_id=data_source_id, **filtered_config)
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            import sqlalchemy
            from sqlalchemy import text
            engine = sqlalchemy.create_engine(self.get_connection_string())
            with engine.connect() as conn:
                conn.execute(text("SELECT 1"))
            return True
        except Exception:
            return False
    
    def to_dict(self, hide_password: bool = False) -> dict:
        """转换为字典"""
        result = super().to_dict()
        if hide_password:
            result['password'] = "***"
        return result


class ExcelConfig(Base, TimestampMixin):
    """Excel配置模型"""
    
    __tablename__ = "excel_config"
    
    # 关联
    data_source_id = Column(Integer, ForeignKey('data_source.id', ondelete="CASCADE"), nullable=False, unique=True)
    
    # 文件信息
    file_path = Column(String(500), nullable=False, comment="文件路径")
    file_name = Column(String(255), nullable=False, comment="文件名")
    file_size = Column(Integer, comment="文件大小")
    sheet_name = Column(String(100), comment="工作表名称")
    
    # 数据配置
    has_header = Column(Boolean, default=True, comment="是否包含标题行")
    header_row = Column(Integer, default=1, comment="标题行")
    data_start_row = Column(Integer, default=2, comment="数据开始行")
    encoding = Column(String(20), default="utf-8", comment="文件编码")
    
    # 字段映射
    field_mapping = Column(JSON, comment="字段映射配置")
    data_types = Column(JSON, comment="数据类型配置")
    
    # 关系
    data_source = relationship("DataSource", back_populates="excel_config")
    
    def get_file_info(self) -> Dict[str, Any]:
        """获取文件信息"""
        import os
        if os.path.exists(self.file_path):
            stat = os.stat(self.file_path)
            return {
                "exists": True,
                "size": stat.st_size,
                "modified_time": stat.st_mtime,
                "created_time": stat.st_ctime
            }
        return {"exists": False}
    
    def validate_file(self) -> bool:
        """验证文件"""
        try:
            import pandas as pd
            
            # 读取Excel文件
            if self.file_path.endswith('.xlsx'):
                df = pd.read_excel(self.file_path, sheet_name=self.sheet_name or 0)
            elif self.file_path.endswith('.xls'):
                df = pd.read_excel(self.file_path, sheet_name=self.sheet_name or 0, engine='openpyxl')
            else:
                return False
            
            # 检查数据行数
            if len(df) < (self.data_start_row - 1):
                return False
            
            return True
        except Exception:
            return False
    
    def to_dict(self) -> dict:
        """转换为字典"""
        result = super().to_dict()
        result['file_info'] = self.get_file_info()
        return result


class DataSourceSyncLog(Base, TimestampMixin):
    """数据源同步日志模型"""
    
    __tablename__ = "data_source_sync_log"
    
    # 关联
    data_source_id = Column(Integer, ForeignKey('data_source.id', ondelete="CASCADE"), nullable=False, comment="数据源ID")
    
    # 同步信息
    sync_type = Column(String(20), comment="同步类型")
    status = Column(String(20), comment="状态")
    start_time = Column(DateTime, comment="开始时间")
    end_time = Column(DateTime, comment="结束时间")
    duration = Column(Integer, comment="持续时间(秒)")
    
    # 结果信息
    record_count = Column(Integer, comment="记录数量")
    success_count = Column(Integer, comment="成功数量")
    error_count = Column(Integer, comment="错误数量")
    
    # 错误信息
    error_message = Column(Text, comment="错误信息")
    error_details = Column(JSON, comment="错误详情")
    
    # 关系
    data_source = relationship("DataSource", back_populates="sync_logs")  # 暂时注释掉以解决冲突
    
    def complete(self, record_count: int = 0, success_count: int = 0, error_count: int = 0, error_message: str = None):
        """完成同步"""
        from datetime import datetime
        self.end_time = datetime.utcnow()
        if self.start_time:
            self.duration = (self.end_time - self.start_time).seconds
        
        self.record_count = record_count
        self.success_count = success_count
        self.error_count = error_count
        self.error_message = error_message
        
        if error_count > 0:
            self.status = "failed"
        else:
            self.status = "completed"


class DataSourcePreviewLog(Base, TimestampMixin):
    """数据源预览日志模型"""
    
    __tablename__ = "data_source_preview_log"
    
    # 关联
    data_source_id = Column(Integer, ForeignKey('data_source.id', ondelete="CASCADE"), nullable=False, comment="数据源ID")
    
    # 预览信息
    preview_type = Column(String(20), comment="预览类型：table_schema, data_preview")
    table_name = Column(String(100), comment="表名（仅数据库类型）")
    sheet_name = Column(String(100), comment="工作表名（仅Excel类型）")
    
    # 预览结果
    status = Column(String(20), comment="状态：success, failed")
    record_count = Column(Integer, default=0, comment="预览记录数")
    column_count = Column(Integer, default=0, comment="字段数量")
    
    # 性能信息
    execution_time = Column(Integer, comment="执行时间(毫秒)")
    
    # 错误信息
    error_message = Column(Text, comment="错误信息")
    error_details = Column(JSON, comment="错误详情")
    
    # 预览数据样本
    sample_data = Column(JSON, comment="预览数据样本")
    
    # 关系
    data_source = relationship("DataSource", back_populates="preview_logs")  # 暂时注释掉以解决冲突
    
    def complete(self, record_count: int = 0, column_count: int = 0, 
                 execution_time: int = 0, error_message: str = None):
        """完成预览"""
        from datetime import datetime
        self.updated_at = datetime.utcnow()
        self.execution_time = execution_time
        self.record_count = record_count
        self.column_count = column_count
        self.error_message = error_message
        
        if error_message:
            self.status = "failed"
        else:
            self.status = "success"